package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.NamingException;

import tools.DBManager;

public class ProductList {

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	private ArrayList<ProductBean> productList = new ArrayList<ProductBean>();

	public ProductList(){
	}
	
	public void setProductsByCategory(int category) {
		
		if(category == 0){
			setAllProducts();
			return;
		}
		
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_product where t_product.Category = %d", category);
			rs = stmt.executeQuery(sqlCmd);

			// fill the list
			while (rs.next()) {
				ProductBean pb = new ProductBean();

				pb.setId(rs.getInt("ID"));
				pb.setName(rs.getString("Name"));
				pb.setCategory(rs.getInt("Category"));
				pb.setPrice(rs.getDouble("Price"));
				pb.setImage(rs.getString("Image"));
				pb.setDesc(rs.getString("Description"));

				productList.add(pb);
			}

		} catch (SQLException sqlException) {

		} catch (NamingException namingException) {

		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
	}

	public void setAllProducts() {
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_product");
			rs = stmt.executeQuery(sqlCmd);

			// fill the list
			while (rs.next()) {
				ProductBean pb = new ProductBean();

				pb.setId(rs.getInt("ID"));
				pb.setName(rs.getString("Name"));
				pb.setCategory(rs.getInt("Category"));
				pb.setPrice(rs.getDouble("Price"));
				pb.setImage(rs.getString("Image"));
				pb.setDesc(rs.getString("Description"));

				productList.add(pb);
			}

		} catch (SQLException sqlException) {

		} catch (NamingException namingException) {

		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
	}
	
	public ArrayList<ProductBean> getProducts(){
		return productList;
	}
	
}
